package com.hulu.yec.sys.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.hulu.yec.common.util.DBUtil;
import com.hulu.yec.sys.entity.MenuEntity;
import com.hulu.yec.sys.user.entity.SysUserEntity;
import com.hulu.yec.sys.user.service.SysUserDao;

/**
 * 登录页面的数据库操作
 * 
 * @author xiaoguaishou
 *
 *         2016年3月12日
 */
public class SysDao {
	public SysUserEntity login(String code, String password) {
		List<SysUserEntity> userList = new ArrayList<SysUserEntity>();
		// 获取登录进来的角色ID
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		String sql = "SELECT u.id,u.state, u.class_id,u.name , r.name AS 'roleName' FROM sys_user u, sys_role r WHERE r.id=u.role_id AND u.code=? AND u.password=?";
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, code);
			preparedStatement.setString(2, password);
			resultSet = preparedStatement.executeQuery();

			while (resultSet.next()) {

				SysUserEntity sysUserEntity = new SysUserEntity();
				sysUserEntity.setId(resultSet.getInt("id"));
				sysUserEntity.setName(resultSet.getString("name"));
				sysUserEntity.setClassId(resultSet.getInt("class_id"));
				sysUserEntity.setRoleName(resultSet.getString("roleName"));
				sysUserEntity.setState(resultSet.getInt("state"));
				userList.add(sysUserEntity);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(connection, preparedStatement, resultSet);

		}
		if (userList.size() == 0) {
			return null;
		} else {
			return userList.get(0);
		}
	}

	/**
	 * 根据UserId查询出roleId
	 * 
	 * @param id
	 * @return
	 */
	public List<SysUserEntity> roleIdlist(int id) {
		List<SysUserEntity> roleIdList = new ArrayList<>();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		String sql = "SELECT role_id FROM sys_user WHERE id=?";
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, id);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				SysUserEntity sysUserEntity = new SysUserEntity();
				sysUserEntity.setRoleId(resultSet.getInt("role_id"));
				;
				roleIdList.add(sysUserEntity);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(connection, preparedStatement, resultSet);
		}
		return roleIdList;
	}

	/**
	 * 个人信息的查看
	 * 
	 * @return
	 */
	public List<SysUserEntity> list(int id) {
		List<SysUserEntity> userList = new ArrayList<SysUserEntity>();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		String sql = "SELECT u.id, u.code, u.name ,u.sex, u.phone,u.email,u.create_time, r.name AS 'role_name' FROM sys_user u, sys_role r WHERE r.id=u.role_id AND u.id = ?";
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, id);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				SysUserEntity sysUserEntity = new SysUserEntity();
				sysUserEntity.setCode(resultSet.getString("code"));
				sysUserEntity.setName(resultSet.getString("name"));
				sysUserEntity.setSex(resultSet.getInt("sex"));
				sysUserEntity.setPhone(resultSet.getString("phone"));
				sysUserEntity.setEmail(resultSet.getString("email"));
				sysUserEntity.setCreateTime(resultSet.getDate("create_time"));
				sysUserEntity.setRoleName(resultSet.getString("role_name"));
				sysUserEntity.setId(resultSet.getInt("id"));
				userList.add(sysUserEntity);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(connection, preparedStatement, resultSet);
		}
		return userList;
	}

	/**
	 * 学生个人信息查看
	 * 
	 * @param id
	 * @return
	 */
	public List<SysUserEntity> list1(int id) {
		List<SysUserEntity> userList = new ArrayList<SysUserEntity>();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		String sql = "SELECT u.code, u.name ,u.sex, u.phone,u.email,u.create_time,r.name AS 'role_name', c.name AS 'class_name' FROM sys_user u, sys_role r ,sys_class c WHERE r.id=u.role_id  AND c.id=u.class_id AND u.id =?";
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, id);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				SysUserEntity sysUserEntity = new SysUserEntity();
				sysUserEntity.setCode(resultSet.getString("code"));
				sysUserEntity.setName(resultSet.getString("name"));
				sysUserEntity.setSex(resultSet.getInt("sex"));
				sysUserEntity.setPhone(resultSet.getString("phone"));
				sysUserEntity.setEmail(resultSet.getString("email"));
				sysUserEntity.setCreateTime(resultSet.getDate("create_time"));
				sysUserEntity.setRoleName(resultSet.getString("role_name"));
				sysUserEntity.setClassName(resultSet.getString("class_name"));
				userList.add(sysUserEntity);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(connection, preparedStatement, resultSet);
		}
		return userList;
	}

	/**
	 * 通过用户id查询并回显
	 * 
	 * @param id
	 * @return
	 */
	public SysUserEntity listById(int id) {
		SysUserEntity sysUserEntity = new SysUserEntity();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		String sql = "SELECT u.id, u.code, u.name ,u.sex, u.phone,u.email,u.create_time, r.name AS 'role_name' FROM sys_user u, sys_role r WHERE r.id=u.role_id AND u.id = ?";
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, id);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				sysUserEntity.setCode(resultSet.getString("code"));
				sysUserEntity.setName(resultSet.getString("name"));
				sysUserEntity.setSex(resultSet.getInt("sex"));
				sysUserEntity.setPhone(resultSet.getString("phone"));
				sysUserEntity.setEmail(resultSet.getString("email"));
				sysUserEntity.setCreateTime(resultSet.getDate("create_time"));
				sysUserEntity.setRoleName(resultSet.getString("role_name"));

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(connection, preparedStatement, resultSet);
		}
		return sysUserEntity;
	}

	/**
	 * 获得学生个人信息并查询出来回显
	 * 
	 * @param id
	 * @return
	 */
	public SysUserEntity studentListById(int id) {
		SysUserEntity sysUserEntity = new SysUserEntity();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		String sql = "SELECT u.code, u.name ,u.sex, u.phone,u.email,u.create_time,r.name AS 'role_name', c.name AS 'class_name' FROM sys_user u, sys_role r ,sys_class c WHERE r.id=u.role_id  AND c.id=u.class_id AND u.id =?";
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, id);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				sysUserEntity.setCode(resultSet.getString("code"));
				sysUserEntity.setName(resultSet.getString("name"));
				sysUserEntity.setSex(resultSet.getInt("sex"));
				sysUserEntity.setPhone(resultSet.getString("phone"));
				sysUserEntity.setEmail(resultSet.getString("email"));
				sysUserEntity.setCreateTime(resultSet.getDate("create_time"));
				sysUserEntity.setRoleName(resultSet.getString("role_name"));
				sysUserEntity.setClassName(resultSet.getString("class_name"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(connection, preparedStatement, resultSet);
		}
		return sysUserEntity;

	}

	/**
	 * 用户信息的修改
	 * 
	 * @param sysUserEntity
	 * @return
	 */
	public int update(SysUserEntity sysUserEntity) {
		// 定义受影响的行数
		int row = 0;
		Connection connection = null;
		PreparedStatement preparedStatement = null;

		String sql = "UPDATE sys_user SET phone=?,email= ? WHERE id=?";

		try {
			connection = DBUtil.getConnection();
			// 创建预编译对象
			preparedStatement = connection.prepareStatement(sql);
			// 设置参数
			preparedStatement.setString(1, sysUserEntity.getPhone());
			preparedStatement.setString(2, sysUserEntity.getEmail());
			preparedStatement.setInt(3, sysUserEntity.getId());
			// 通过编译对象执行SQl指令
			row = preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(connection, preparedStatement);
		}
		return row;
	}

	/**
	 * 修改密码之前的查询
	 * 
	 * @param oldpassword
	 * @param password
	 * @return
	 */
	public List<SysUserEntity> lookupPassword(int id, String oldpassword) {
		List<SysUserEntity> roleIdList = new ArrayList<>();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		String sql = "SELECT password FROM sys_user WHERE  id = ? AND password =? ";

		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, id);
			preparedStatement.setString(2, oldpassword);

			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				SysUserEntity sysUserEntity = new SysUserEntity();

				sysUserEntity.setPassword(resultSet.getString("password"));
				roleIdList.add(sysUserEntity);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(connection, preparedStatement, resultSet);
		}
		return roleIdList;
	}

	/**
	 * 密码修改
	 * 
	 * @param password1
	 * @param id
	 * @param sysUser
	 * @return
	 */
	public int modifyPassword(int id, String password1) {
		// 定义受影响的行数
		Connection connection = null;
		PreparedStatement preparedStatement = null;

		String sql = "UPDATE sys_user SET password = ? WHERE id = ?";

		try {
			connection = DBUtil.getConnection();
			// 创建预编译对象
			preparedStatement = connection.prepareStatement(sql);
			// 设置参数
			preparedStatement.setString(1, password1);
			preparedStatement.setInt(2, id);
			// 通过编译对象执行SQl指令
			return preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(connection, preparedStatement);
		}
		return id;
	}
	
	/**
	 * 通过id获取该用户的根菜单（权限）
	 * 
	 * @param id
	 * @return
	 */
	public List<MenuEntity> getMainMenu( int id){
			String sql="SELECT * from sys_menu WHERE id in(SELECT menu_id FROM role_menu WHERE role_id=(SELECT role_id from sys_user WHERE id = ?)) AND is_leaf=2";
			return  new SysUserDao().excuteQuery(MenuEntity.class, sql, id);
		}
	/**
	 * 通过id获取该用户的子菜单（权限）
	 * 
	 * @param id
	 * @return
	 */
	public List<MenuEntity> getChildMenu( int id){
			String sql="SELECT * from sys_menu WHERE id in(SELECT menu_id FROM role_menu WHERE role_id=(SELECT role_id from sys_user WHERE id = ?)) AND is_leaf=1";
			return  new SysUserDao().excuteQuery(MenuEntity.class, sql, id);
		}

	
		/**
		 * 通过id获取邮箱
		 * @param email
		 * @param phone 
		 * @return
		 */
	public List<SysUserEntity> verification(String email) {
		List<SysUserEntity> verificationList = new ArrayList<>();
		
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		String sql = "SELECT id FROM sys_user WHERE email =?";
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, email);
					resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				SysUserEntity sysUser = new SysUserEntity();
				sysUser.setId(resultSet.getInt("id"));
				verificationList.add(sysUser);
				}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(connection, preparedStatement, resultSet);
		}
		return verificationList;
	}
/**
 * 通过id获取电话
 * @param phone
 * @return
 */
		public List<SysUserEntity> verificationPhoen(String phone) {
			List<SysUserEntity> verificationList = new ArrayList<>();
			
			Connection connection = null;
			PreparedStatement preparedStatement = null;
			ResultSet resultSet = null;
			String sql = "SELECT id FROM sys_user WHERE phone =?";
			try {
				connection = DBUtil.getConnection();
				preparedStatement = connection.prepareStatement(sql);
				preparedStatement.setString(1, phone);
				resultSet = preparedStatement.executeQuery();
				while (resultSet.next()) {
					SysUserEntity sysUser = new SysUserEntity();
					sysUser.setId(resultSet.getInt("id"));
					verificationList.add(sysUser);
					}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				DBUtil.close(connection, preparedStatement, resultSet);
			}
			return verificationList;
		}

}
